import mysql.connector
import hashlib
import itertools
import json
from typing import List, Dict, Tuple
import pymysql as mysql_connector



class DatabaseQueryGenerator:
    def __init__(self, host: str, user: str, password: str, database: str, port:int):
        """初始化数据库连接"""
        self.host = host
        self.user = user
        self.port = port
        self.password = password
        self.database = database
        self.connection = None

    def connect(self):
        """连接数据库"""
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                port =self.port,
                charset='utf8mb4'
            )
            print("数据库连接成功!")
            return True
        except Exception as err:
            print(f"数据库连接失败: {err}")
            return False

    def query_desk_template_plan(self) -> List[Dict]:
        """查询desk_template_plan表数据"""
        if not self.connection:
            print("请先连接数据库")
            return []

        try:
            cursor = self.connection.cursor(dictionary=True)
            query = "select  * from desk_template_plan where deleted=0 and subject_id like '%1%' and id in ( 274,279,280,295,306);"
            cursor.execute(query)
            results = cursor.fetchall()
            cursor.close()
            print(f"查询到 {len(results)} 条记录")
            return results
        except Exception as err:
            print(f"查询失败: {err}")
            return []

    def close(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            print("数据库连接已关闭")


def md5_hash(text: str) -> str:
    """
    使用Python的hashlib生成MD5哈希
    这与提供的JavaScript MD5函数效果相同
    """
    return hashlib.md5(text.encode('utf-8')).hexdigest().lower()


def extract_abstract_name(name: str, max_length: int = 20) -> str:
    """
    从计划名称中提取抽象名称，限制在指定长度内
    """
    # 移除常见的后缀词汇
    suffixes_to_remove = ['综合复习', '专题训练', '基础练习', '提高练习', '模拟测试']

    abstract_name = name
    for suffix in suffixes_to_remove:
        if abstract_name.endswith(suffix):
            abstract_name = abstract_name[:-len(suffix)]
            break

    # 如果还是太长，截取前面部分
    if len(abstract_name) > max_length:
        abstract_name = abstract_name[:max_length]

    return abstract_name.strip()


def generate_combinations_optimized(data: List[Dict], max_combination_size: int = 3, batch_size: int = 1000):
    """
    内存优化的排列组合生成器
    使用生成器模式，避免一次性加载所有组合到内存

    Args:
        data: 原始数据列表
        max_combination_size: 最大组合长度，避免生成过多组合
        batch_size: 批处理大小，控制内存使用

    Yields:
        组合的批次列表
    """
    total_combinations = 0

    # 计算预期的组合总数
    for r in range(1, min(len(data) + 1, max_combination_size + 1)):
        if r <= len(data):
            # 排列数公式: P(n,r) = n!/(n-r)!
            perm_count = 1
            for i in range(len(data), len(data) - r, -1):
                perm_count *= i
            total_combinations += perm_count

    print(f"限制组合长度为{max_combination_size}，预计生成{total_combinations}个组合")

    # 如果组合数量太大，警告用户
    if total_combinations > 10000:
        print(f"警告: 组合数量较大({total_combinations})，建议减少max_combination_size参数")
        response = input("是否继续? (y/n): ")
        if response.lower() != 'y':
            return

    batch = []
    count = 0

    # 生成限制长度的组合
    for r in range(1, min(len(data) + 1, max_combination_size + 1)):
        print(f"正在生成长度为{r}的排列...")

        for combo in itertools.permutations(data, r):
            batch.append(combo)
            count += 1

            # 达到批次大小时，返回当前批次
            if len(batch) >= batch_size:
                yield batch
                batch = []

    # 返回最后一个批次（如果不为空）
    if batch:
        yield batch

    print(f"总共生成了{count}个组合")


def generate_combinations_simple(data: List[Dict], max_size: int = 2):
    """
    简化版组合生成器，只生成常用的组合
    减少内存占用和计算时间

    Args:
        data: 原始数据列表
        max_size: 最大组合大小，默认为2

    Returns:
        组合列表
    """
    combinations = []

    # 只生成单个和成对的组合
    for r in range(1, min(len(data) + 1, max_size + 1)):
        for combo in itertools.permutations(data, r):
            combinations.append(combo)

    return combinations


def generate_combinations_by_pairs(data: List[Dict]):
    """
    专门生成成对组合的优化版本
    适用于只需要两两组合的场景
    """
    combinations = []

    # 单个元素
    for item in data:
        combinations.append((item,))

    # 成对元素（考虑顺序）
    for i in range(len(data)):
        for j in range(len(data)):
            if i != j:  # 避免自己和自己组合
                combinations.append((data[i], data[j]))

    return combinations


def create_insert_sql_batch(combination_batch: List[Tuple], start_id: int) -> Tuple[List[str], int]:
    """
    为批次组合创建INSERT SQL语句
    返回SQL语句列表和下一个ID
    """
    insert_statements = []
    current_id = start_id

    for combo in combination_batch:
        # 提取ID列表
        plan_ids = [str(item['id']) for item in combo]
        plan_id_str = ','.join(plan_ids)

        # 生成MD5哈希
        code = md5_hash(''.join(plan_ids))

        # 提取抽象名称
        if len(combo) == 1:
            name = extract_abstract_name(combo[0]['name'])
        else:
            # 多个计划的组合，取第一个的抽象名称并加上"组合"
            first_name = extract_abstract_name(combo[0]['name'], 15)
            name = f"{first_name}组合"

        # 创建INSERT语句
        insert_sql = f"""INSERT INTO diagnostic_template_plan (id, name, code, desk_template, biz_code, deleted, create_time, update_time) 
VALUES ({current_id}, '{name}', '{code}', '{plan_id_str}', 'desk', 0, NOW(), NOW());"""

        insert_statements.append(insert_sql)
        current_id += 1

    return insert_statements, current_id


def main():
    """主函数 - 内存优化版本"""
    # 数据库连接配置 - 请根据实际情况修改
    db_config = {
        'host': 'rm-bp1537rn4692e8d1r.mysql.rds.aliyuncs.com',
        'user': 'dev_user',
        'password': 'Ewt360@com',
        'database': 'studyprod',
        'port':3306
    }

    # 如果无法连接数据库，使用示例数据
    use_sample_data = False  # 设置为False以使用真实数据库

    if use_sample_data:
        print("使用示例数据进行演示...")
        # 示例数据
        sample_data = [
            {"id": 19, "name": "一元二次函数、方程和不等式综合复习"},
            {"id": 20, "name": "函数与方程基础练习"},
            {"id": 21, "name": "不等式专题训练"}
        ]
        data = sample_data
    else:
        # 使用真实数据库
        db_generator = DatabaseQueryGenerator(**db_config)

        if not db_generator.connect():
            print("无法连接数据库，程序退出")
            return

        # 查询数据
        data = db_generator.query_desk_template_plan()
        db_generator.close()

        if not data:
            print("没有查询到数据")
            return

    # 显示查询结果的JSON格式
    print("\n查询结果JSON格式:")
    json_result = [{"id": item["id"], "name": item["name"]} for item in data]
    print(json.dumps(json_result, ensure_ascii=False, indent=2))

    # 选择组合生成策略
    print(f"\n数据量: {len(data)} 条")
    print("请选择组合生成策略:")
    print("1. 简化版(最大2个元素组合) - 推荐")
    print("2. 成对组合版(单个+成对)")
    print("3. 批处理版(可设置最大组合长度)")

    choice = input("请选择 (1/2/3): ").strip()

    all_insert_statements = []

    if choice == "1":
        # 简化版
        print("使用简化版组合生成...")
        combinations = generate_combinations_simple(data, max_size=2)
        print(f"生成 {len(combinations)} 个组合")
        all_insert_statements = create_insert_sql(combinations)

    elif choice == "2":
        # 成对组合版
        print("使用成对组合生成...")
        combinations = generate_combinations_by_pairs(data)
        print(f"生成 {len(combinations)} 个组合")
        all_insert_statements = create_insert_sql(combinations)

    elif choice == "3":
        # 批处理版
        max_size = int(input("请输入最大组合长度 (建议不超过3): ") or "3")
        batch_size = int(input("请输入批处理大小 (默认1000): ") or "1000")

        print("使用批处理版组合生成...")
        current_id = 80
        total_statements = 0

        # 使用生成器批量处理
        with open('insert_statements.sql', 'w', encoding='utf-8') as f:
            f.write("-- 诊断模板计划表插入语句\n")
            f.write(f"-- 批处理生成，最大组合长度: {max_size}\n\n")

            for batch in generate_combinations_optimized(data, max_size, batch_size):
                batch_statements, current_id = create_insert_sql_batch(batch, current_id)

                # 直接写入文件，不占用内存
                for sql in batch_statements:
                    f.write(sql + '\n')

                total_statements += len(batch_statements)
                print(f"已处理 {total_statements} 条记录...")

        print(f"\n所有 {total_statements} 条INSERT语句已保存到 'insert_statements.sql' 文件中")
        return

    else:
        print("无效选择，使用默认简化版")
        combinations = generate_combinations_simple(data, max_size=2)
        all_insert_statements = create_insert_sql(combinations)

    # 输出前几个示例
    print(f"\n前5个INSERT语句示例:")
    for i, sql in enumerate(all_insert_statements[:5]):
        print(f"{i + 1}. {sql}")

    # 保存到文件
    with open('insert_statements.sql', 'w', encoding='utf-8') as f:
        f.write("-- 诊断模板计划表插入语句\n")
        f.write("-- 总计: {} 条记录\n\n".format(len(all_insert_statements)))
        for sql in all_insert_statements:
            f.write(sql + '\n')

    print(f"\n所有 {len(all_insert_statements)} 条INSERT语句已保存到 'insert_statements.sql' 文件中")

    # 显示一些统计信息
    print(f"\n统计信息:")
    print(f"- 原始数据: {len(data)} 条")
    print(f"- 生成组合: {len(all_insert_statements)} 个")
    print(f"- 起始ID: 16")
    print(f"- 结束ID: {15 + len(all_insert_statements)}")


def create_insert_sql(combinations: List[Tuple], start_id: int = 16) -> List[str]:
    """
    为所有组合创建INSERT SQL语句（兼容版本）
    """
    insert_statements = []
    current_id = start_id

    for combo in combinations:
        # 提取ID列表
        plan_ids = [str(item['id']) for item in combo]
        plan_id_str = ','.join(plan_ids)

        # 生成MD5哈希
        code = md5_hash(plan_id_str)

        # 提取抽象名称
        if len(combo) == 1:
            name = extract_abstract_name(combo[0]['name'])
        else:
            # 多个计划的组合，取第一个的抽象名称并加上"组合"
            first_name = extract_abstract_name(combo[0]['name'], 15)
            name = f"{first_name}组合"

        # 创建INSERT语句
        insert_sql = f"""INSERT INTO diagnostic_template_plan (id, name, code, desk_template, biz_code, deleted, create_time, update_time) 
VALUES ({current_id}, '{name}', '{code}', '{plan_id_str}', 'desk', 0, NOW(), NOW());"""

        insert_statements.append(insert_sql)
        current_id += 1

    return insert_statements


if __name__ == "__main__":
    main()


# 单独测试MD5函数的示例
def test_md5():
    """测试MD5函数"""
    print("\nMD5测试:")
    test_cases = ["19", "19,20", "20,19", "19,20,21"]
    for case in test_cases:
        result = md5_hash(case)
        print(f"MD5('{case}') = {result}")